package com.sihan.swvp.sync.task;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.stereotype.Component;

import com.sihan.swvp.common.CommonUtil;

/**
 * JDBC 数据同步器
 * @author jch
 *
 */
@Component
public class JdbcSynchronizer extends TaskSynchronizer {

	private String driver;
	private String url;
	private String user;
	private String password;
	private String rawtable;//数据来源表
	private String timestamp;//数据来源表时间戳
	
	private String datatable;//本地数据表
	private String datakey;//本地数据关键字，将通过关键字进行更新

    QueryRunner queryRunner = new QueryRunner();  
	
   
	
    @Override
	public void sync() throws Exception {
    	Date syncDate = new Date();
    	System.out.println("开始同步数据...");
    	try {
    		List<Map<String, Object>> raws = syncRaw();
        	saveData(raws);
        	
        	syncService.updateSyncTime(jobid, syncDate,"成功",true);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			syncService.updateSyncTime(jobid, syncDate,"失败，"+e.getMessage(),false);
			throw e;
		}
	}

	@Override
	public void sync(Date starttime, Date endtime) throws Exception {
		Date syncDate = new Date();
		try {
			List<Map<String, Object>> raws = syncRaw(starttime,endtime);
	    	saveData(raws);
	    	syncService.updateSyncTime(jobid, syncDate,"手动同步成功",false);
		} catch (Exception e) {
			// TODO: handle exception
			syncService.updateSyncTime(jobid, syncDate,"手动同步失败，"+e.getMessage(),false);
			
			throw e;
		}
	}

	/**
	 * 同步原始数据
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> syncRaw() throws Exception {
		
		return syncRaw(getLastDate(),null);
	}
	
	/**
	 * 同步原始数据
	 * @param starttime 开始时间
	 * @param endtime 结束时间
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> syncRaw(Date starttime,Date endtime) throws Exception {
		Connection conn = null;
		try {
			conn = CommonUtil.getConnection(driver, url, user, password);
			StringBuilder sqlb = new StringBuilder();
			sqlb.append("select ");
			for (int i = 0; i < rawfields.length; i++) {
				sqlb.append(i==0 ? "" : ",").append(rawfields[i]);
			}
			sqlb.append(" from ").append(rawtable)
			.append(" where ").append(timestamp).append(" >=").append("'").append(CommonUtil.date2string(starttime)).append("'");
			if(null != endtime){
				sqlb.append(" and ").append(timestamp).append(" < ").append("'").append(CommonUtil.date2string(endtime)).append("'");
			}
			
			List<Map<String, Object>> datas = queryRunner.query(conn, sqlb.toString(), new MapListHandler());
			
			return datas;
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new Exception("没有找到数据库驱动:"+driver);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
			throw e;
		}
		finally{
			
			CommonUtil.close(conn);
		}
	}
	
	/**
	 * 保存数据
	 * @param raws
	 */
	public void saveData(List<Map<String, Object>> raws) {
		for (Map<String, Object> raw : raws) {
			Map<String, Object> data = transfer(raw);
			Map<String,Object> params = new HashMap<String,Object>();
			params.put(datakey, data.get(datakey));
			List<Map<String,Object>> datas = syncService.exeSelection(datatable, datafields, params);
			if(datas.isEmpty()){
				Object[] values = new Object[datafields.length];
				for (int i = 0; i < datafields.length; i++) {
					values[i] = data.get(datafields[i]);
				}
				syncService.exeInsert(datatable, datafields, values, params);
			}
			else{
				syncService.exeUpdate(datatable, data, params);
			}
		}
		
	}

	public String getDriver() {
		return driver;
	}

	public void setDriver(String driver) {
		this.driver = driver;
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}


	public String getTimestamp() {
		return timestamp;
	}

	public void setTimestamp(String timestamp) {
		this.timestamp = timestamp;
	}

	public String getDatakey() {
		return datakey;
	}

	public void setDatakey(String datakey) {
		this.datakey = datakey;
	}

	public String getRawtable() {
		return rawtable;
	}

	public void setRawtable(String rawtable) {
		this.rawtable = rawtable;
	}

	public String getDatatable() {
		return datatable;
	}

	public void setDatatable(String datatable) {
		this.datatable = datatable;
	}
}
